<%@ include file="Common.jsp" %><%!
//
//   Filename: Ranking_WM.jsp
//   Generated with CodeCharge  v.1.2.0
//   JSP.ccp build 05/21/01
//

static final String sFileName = "Ranking_WM.jsp";
              
%><%

boolean bDebug = false;

String sAction = getParam( request, "FormAction");
String sForm = getParam( request, "FormName");
String sSearchErr = "";
String smembersErr = "";

java.sql.Connection conn = null;
java.sql.Statement stat = null;
String sErr = loadDriver();
conn = cn();
stat = conn.createStatement();
if ( ! sErr.equals("") ) {
 try {
   out.println(sErr);
 }
 catch (Exception e) {}
}

%>            
<html>
<head>
<title>Zoccer, das Fussball Tippspiel - Rangliste</title>
</head>

<body style="color: #000000; font-family: Arial, Tahoma, Verdana, Helveticabackground-color: #FFFFFF; color: #000000; font-family: Arial, Tahoma, Verdana, Helvetica">
<jsp:include page="Header.jsp" flush="true"/><center>

<%
	out.println("&nbsp;<br><br>");
	out.println("<table>");      
    out.print("<tr>");
    String fldField1 = "Ranking_WM.jsp";
    String fldField2 = "Standings.jsp";
    // Show fields                      
    out.print("<td><a href=\""+fldField2+"\"><font style=\"font-size: 12pt; color: #000000\"><b>Gruppen</b></font></a>");
    out.print("<font style=\"font-size: 12pt; color: #000000\"><b> | </b></font></td>");
    out.print("<td><a href=\""+fldField1+"\"><font style=\"font-size: 12pt; color: #000000\"><b>Rangliste</b></font></a>");
    out.println("</tr></table>");
    out.println("<br><br>");
%>    	

 <table>
  <tr>   
   <td valign="top">
		<% Search_Show(request, response, session, out, sSearchErr, sForm, sAction, conn, stat); %>    
   </td>
  </tr>
 </table>

 <table>
  <tr>
   <td valign="top">
		<% members_Show(request, response, session, out, smembersErr, sForm, sAction, conn, stat); %>    
   </td>
  </tr>
 </table>

 <% out.println(getRandomCit() + "<br>"); %>

<jsp:include page="Footer.jsp" flush="true"/>
</center>
</body>
</html>
<%%>
<%
if ( stat != null ) stat.close();
if ( conn != null ) conn.close();
%>
<%!

  void Search_Show (javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response, javax.servlet.http.HttpSession session, javax.servlet.jsp.JspWriter out, String sSearchErr, String sForm, String sAction, java.sql.Connection conn, java.sql.Statement stat) throws java.io.IOException {
    try {
      
      String fldmember_login="";

      String sSQL="";
      String transitParams = "";
      String sQueryString = "";
      String sPage = "";
      
      out.println("<table bgcolor=\"#000000\" cellspacing=\"1\" cellpadding=\"1\">");
      out.println("<tr>\n<td style=\"background:rgb(235,235,235); text-align: Center\" colspan=\"2\"><font style=\"font-size: 12pt; font-weight: bold\">Suche</font></td>\n</tr>");
	  out.println("     <form method=\"get\" action=\"Ranking_WM.jsp\" name=\"Search\">\n     <tr>");

      // Set variables with search parameters
      fldmember_login = getParam( request, "member_login");

      // Show fields
      out.println("<td style=\"background:rgb(235,235,235);\"><font style=\"font-size: 10pt; color: #ffffff\"> EIN </font></td>");
      out.println("<td style=\"background:rgb(235,235,235);\">");
	  out.println("<input type=\"text\"  name=\"member_login\" maxlength=\"15\" value=\""+toHTML(fldmember_login)+"\" size=\"\">");
	  out.println("</td>\n     </tr>\n     <tr>");
      
      out.println("<td style=\"background:rgb(235,235,235);\" colspan=\"2\"><input type=\"submit\" value=\"Search\"/></td>");
      out.println("</tr>\n     </form>\n    </table>");
      out.println("");
    }
    catch (Exception e) { out.println(e.toString()); }
  }


  void members_Show (javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response, javax.servlet.http.HttpSession session, javax.servlet.jsp.JspWriter out, String smembersErr, String sForm, String sAction, java.sql.Connection conn, java.sql.Statement stat) throws java.io.IOException  {
  
    String sWhere = "";
    int iCounter=0;
    int iPage = 0;
    boolean bIsScroll = true;
    boolean hasParam = false;
    String sOrder = "";
    String sSQL="";
    String transitParams = "";
    String sQueryString = "";
    String sPage = "";
    int RecordsPerPage = 15;
    String sSortParams = "";
    String formParams = "";

      String pmember_login="";

    transitParams = "member_login=" + toURL(getParam( request, "member_login")) + "&";
    formParams = "member_login=" + toURL(getParam( request, "member_login")) + "&"; 

    // Build WHERE statement
  
    //-- Check member_login parameter and create a valid sql for where clause
  
    pmember_login = getParam( request, "member_login");
    if (pmember_login != null && ! pmember_login.equals("")) {
            
      if (! sWhere.equals("")) sWhere += " and ";
        hasParam = true;
        sWhere += "m.member_login like '%" + replace(pmember_login, "'", "''") + "%'";
    }
    
    if (hasParam) { sWhere = " AND (" + sWhere + ")"; }
    // Build ORDER statement
    sOrder = " order by m_current_score DESC, m_nickname ASC";
    String sSort = getParam( request, "Formmembers_Sorting");
    String sSorted = getParam( request, "Formmembers_Sorted");
    String sDirection = "";
    String sForm_Sorting = "";
    int iSort = 0;
    try {
      iSort = Integer.parseInt(sSort);
    }
    catch (NumberFormatException e ) {
      sSort = "";
    }
    if ( iSort == 0 ) {
      sForm_Sorting = "";
    }
    else {
      if ( sSort.equals(sSorted)) { 
        sSorted="0";
        sForm_Sorting = "";
        sDirection = " DESC";
        sSortParams = "Formmembers_Sorting=" + sSort + "&Formmembers_Sorted=" + sSort + "&";
      }
      else {
        sSorted=sSort;
        sForm_Sorting = sSort;
        sDirection = " ASC";
        sSortParams = "Formmembers_Sorting=" + sSort + "&Formmembers_Sorted=" + "&";
      }
    
      if ( iSort == 1) { sOrder = " order by m_nickname" + sDirection; }
      if ( iSort == 2) { sOrder = " order by m_last_name" + sDirection + ", m_first_name ASC"; }
      if ( iSort == 3) { sOrder = " order by m_member_login" + sDirection; }
      if ( iSort == 4) { sOrder = " order by m_current_score" + sDirection + ", m_nickname ASC"; }
    }
  

  // Build full SQL statement
  String regFlag = "1";
  sSQL = "select " +
  	"m.current_rank as m_rank, " +
  	"m.nickname as m_nickname, " +
  	"m.first_name as m_first_name, " +
  	"m.last_name  as m_last_name, " +
  	"m.member_login as m_member_login, " +
  	"m.current_score as m_current_score " +
  	"from members m " +
  	"where m.registered = " + regFlag;

	sSQL = sSQL + sWhere + sOrder;

  String sNoRecords = "     <tr>\n      <td colspan=\"5\" style=\"background-color: #FFFFEE; border-style: inset; border-width: 1\"><font style=\"font-size: 10pt; color: #000000\">No records</font></td>\n     </tr>";


  String tableHeader = "";
	tableHeader =  "      <tr>\n";
	tableHeader += "      <td style=\"background:rgb(235,235,235)\" width=\"50\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Rang</font></td>\n";
	tableHeader += "      <td style=\"background:rgb(235,235,235)\" width=\"150\"><a href=\""+sFileName+"?"+formParams+"Formmembers_Sorting=1&Formmembers_Sorted="+sSorted+"&\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Nickname</font></a></td>\n";
	//tableHeader += "      <td style=\"background:rgb(235,235,235)\" width=\"150\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Vorname</font></td>\n";
	//tableHeader += "      <td style=\"background:rgb(235,235,235)\" width=\"150\"><a href=\""+sFileName+"?"+formParams+"Formmembers_Sorting=2&Formmembers_Sorted="+sSorted+"&\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Name</font></a></td>\n";
	tableHeader += "      <td style=\"background:rgb(235,235,235)\" width=\"100\"><a href=\""+sFileName+"?"+formParams+"Formmembers_Sorting=3&Formmembers_Sorted="+sSorted+"&\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">EIN</font></a></td>\n";
	tableHeader += "      <td style=\"background:rgb(235,235,235)\" width=\"50\"><a href=\""+sFileName+"?"+formParams+"Formmembers_Sorting=4&Formmembers_Sorted="+sSorted+"&\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Punkte</font></a></td>\n";
	tableHeader += "      </tr>";
  
  
  try {
    out.println("<br>");
	out.println("<br>");
    out.println("<table bgcolor=\"#000000\" cellspacing=\"1\" cellpadding=\"1\">");
    out.println("     <tr>\n      <td style=\"background:rgb(235,235,235); text-align: Center\" colspan=\"4\"><a name=\"Members\"><font style=\"font-size: 12pt; font-weight: bold\">Rangliste</font></a></td>\n     </tr>");
    out.println(tableHeader);

  }
  catch (Exception e) {}

  
  try {
    // Select current page
    iPage = Integer.parseInt(getParam( request, "Formmembers_Page"));
  }
  catch (NumberFormatException e ) {
    iPage = 0;
  }

  if (iPage == 0) { iPage = 1; }

  try {
    java.sql.ResultSet rs = null;
    // Open recordset
    rs = openrs( stat, sSQL);
    iCounter = 0;
    absolute (rs, (iPage-1)*RecordsPerPage+1);
    java.util.Hashtable rsHash = new java.util.Hashtable();
    String[] aFields = getFieldsName( rs );

    // Show main table based on recordset
    while ( (iCounter < RecordsPerPage) && rs.next() ) {

      getRecordToHash( rs, rsHash, aFields );
      String fldrank = (String) rsHash.get("m_rank");
      String fldnickname = (String) rsHash.get("m_nickname");
      String fldfirst_name = (String) rsHash.get("m_first_name");
      String fldlast_name = (String) rsHash.get("m_last_name");
      String fldmember_login = (String) rsHash.get("m_member_login");
      String fldcurrent_score = (String) rsHash.get("m_current_score");

      out.println("     <tr>");

      out.print("      <td style=\"background-color: #FFFFEE\"><font style=\"font-size: 12pt; color: #000000\"><b>"); out.print(toHTML(fldrank)+"</b></font>");
      out.println("</td>");      
      out.print("      <td style=\"background-color: #FFFFEE\"><font style=\"font-size: 12pt; color: #000000\"><b>"); out.print(toHTML(fldnickname)+"</b></font>");
      out.println("</td>");
      //out.print("      <td style=\"background-color: #FFFFEE\"><font style=\"font-size: 12pt; color: #000000\"><b>"); out.print(toHTML(fldfirst_name)+"</b></font>");
      //out.println("</td>");
      //out.print("      <td style=\"background-color: #FFFFEE\"><font style=\"font-size: 12pt; color: #000000\"><b>"); out.print(toHTML(fldlast_name)+"</b></font>");
      //out.println("</td>");
      out.print("      <td style=\"background-color: #FFFFEE\"><font style=\"font-size: 12pt; color: #000000\"><b>"); out.print(toHTML(fldmember_login)+"</b></font>");
      out.println("</td>");      
      out.print("      <td style=\"background-color: #FFFFEE\"><font style=\"font-size: 12pt; color: #000000\"><b>"); out.print(toHTML(fldcurrent_score)+"</b></font>");
      out.println("</td>");      
      out.println("     </tr>");
    
      iCounter++;
    }
    if (iCounter == 0) {
      // Recordset is empty
      out.println(sNoRecords);
    
      out.print("     <tr>\n      <td colspan=\"4\" style=\"background:rgb(235,235,235)\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">");
      out.println("</td>\n     </tr>");
    
      iCounter = RecordsPerPage+1;
      bIsScroll = false;
    }

    else {
  
    // Parse scroller
    boolean bInsert = false;
    boolean bNext = rs.next();
    if ( !bNext && iPage == 1 ) {
    
	  out.print("     <tr>\n      <td colspan=\"4\" style=\"background:rgb(235,235,235)\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">");
      out.println("\n      </td>\n     </tr>");
    
    }
    else {
      out.print("     <tr>\n      <td colspan=\"4\" style=\"background:rgb(235,235,235)\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">");
      bInsert = true;
    
      if ( iPage == 1 ) {
        out.print("\n       <a href_=\"#\"><font style=\"font-size: 10pt; color: #000000; font-weight: bold\">Previous</font></a>");
      }
      else {
        out.print("\n       <a href=\""+sFileName+"?"+formParams+sSortParams+"Formmembers_Page="+(iPage - 1)+"#Form\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Previous</font></a>");
      }
  
      out.print("\n       [ "+iPage+" ]");
  
      if (!bNext) { 
        out.print("\n       <a href_=\"#\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Next</font></a><br>");
      }
      else {
        out.print("\n       <a href=\""+sFileName+"?"+formParams+sSortParams+"Formmembers_Page="+(iPage + 1)+"#Form\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Next</font></a><br>");
      }
    
      if ( ! bInsert ) {
        out.print("     <tr>\n      <td colspan=\"4\" style=\"background:rgb(235,235,235)\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">");
      }
    
      out.println("</td>\n     </tr>");
    }
  
    }

    if ( rs != null ) rs.close();
    
    out.println("    </table> &nbsp <br><br>");
    //out.println("<br>");
    //out.println(getRandomCit() + "<br>");
    
  }
  catch (Exception e) { out.println(e.toString()); }
}
%>
